﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;
namespace U8
{
    public class Inventory1
    {
        string strConn = ConfigurationManager.ConnectionStrings["conn1"].ConnectionString;
        string UserId = ConfigurationManager.ConnectionStrings["str"].ConnectionString;
        public Inventory1()
        {
            // this.strConn = strConn;
            //  this.UserId = UserId;
        }

        public string Save(InventoryInfo bom,ref long bomid)
        {
            DataSet ds = GetModel(bom.cInvCode);
            foreach (DataTable table in ds.Tables)
            {
                string tblName = table.TableName.ToLower();
                foreach (DataRow dr in table.Rows)
                {
                    if (tblName == "bas_part")
                    {
                        dr["invcode"] = bom.cInvCode;
                        for (int i = 1; i <= 10; i++)
                        {
                            dr["free" + i] = "";
                        }
                    }

                }
            }
            return Save(ds, bom,ref bomid);
        }


        /// <summary>
        /// 保存存货档案
        /// </summary>
        /// <param name="dsData"></param>
        /// <param name="bom"></param>
        /// <returns></returns>
        public string Save(DataSet dsData, InventoryInfo bom,ref long bomid)
        {

            SqlConnection Conn = new SqlConnection(strConn);
            Conn.Open();
            SqlTransaction tran = Conn.BeginTransaction();
            try
            {



                {
                    int i = 0; // 锁住存货属性是否正常，并且锁住表
                    string query = "SELECT * from inventory WITH (UPDLOCK)  where cinvcode='" + bom.cInvCode + "'  and  bBomMain=1";
                    using (SqlCommand cmd = new SqlCommand(query, Conn, tran))
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int aa = reader.FieldCount;
                        while (reader.Read())
                        {
                            string a = reader["cInvCCode"].ToString();
                            i++;
                        }
                    }
                    if (i == 0)
                    {
                        return bom.cInvCode + "存货母件不存在或者不是母件属性";
                    }

                }




                //锁子表查询
                foreach (var row in bom.InventorySub)
                {
                    int i = 0;
                    string query = "SELECT *  from inventory WITH (UPDLOCK) where cinvcode='" + bom.InventorySub[i].cInvCodeSub + "' and  bBomSub=1";
                    using (SqlCommand cmd = new SqlCommand(query, Conn, tran))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            int aa = reader.FieldCount;
                            while (reader.Read())
                            {
                                i++;
                            }
                        }
                    }
                    if (i == 0)
                    {
                        return bom.InventorySub[i].cInvCodeSub + "存货子件不存在或者不是母件属性";
                    }
                }




                //string bomid = "";
                // Save(dsData, DBName, Conn, tran);
                // if (!ExistsBom(dsData.Tables["bas_Part"].Rows[0]["PartId"].ToString(), UserId))
                if (bom.BomID != null)  //如果存在同版本的数据，先删除再新增
                {
                    Quary("delete bom_opcomponentopt where OptionsId in (select  OptionsId from bom_opcomponent  where BomId = " + bom.BomID + ")", Conn, tran);
                    Quary("delete bom_opcomponent_extradefine where OpComponentId in (select  OpComponentId from bom_opcomponent  where BomId = " + bom.BomID + ")", Conn, tran);
                    Quary("delete bom_bom  where BomId=" + bom.BomID, Conn, tran);
                    Quary("delete bom_parent  where BomId=" + bom.BomID, Conn, tran);
                    Quary("delete bom_opcomponent  where BomId=" + bom.BomID, Conn, tran);
                    Quary("delete bom_bom_extradefine where Bomid=" + bom.BomID, Conn, tran);
                }
                SaveBom(dsData.Tables["bas_Part"], bom, UserId, Conn, tran,ref bomid);




                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return ex.Message;
            }
            return "";
        }

        /// <summary>
        /// 保存BOM信息
        /// </summary>
        /// <param name="dtPart"></param>
        /// <param name="bom"></param>
        /// <param name="Conn"></param>
        /// <param name="Tran"></param>
        public void SaveBom(DataTable dtPart, InventoryInfo bom, string DBName, SqlConnection Conn, SqlTransaction Tran,ref long BomId)
        {
            DataSet ds = GetBOMModel();
         //   long BomId = 0L;
            GetVoucherID(DBName.Substring(7, 3), "bom_bom", ref BomId);
            {
                DataRow dr = ds.Tables["bom_parent"].NewRow();
                dr["AutoId"] = Guid.NewGuid();
                dr["BomId"] = BomId;
                dr["ParentId"] = dtPart.Rows[0]["PartId"];
                dr["ParentScrap"] = bom.ParentScrap;  //母件损耗
                dr["SharingPartId"] = 0;
                ds.Tables["bom_parent"].Rows.Add(dr);
            }


            {
                DataRow dr = ds.Tables["bom_bom_extradefine"].NewRow();
                dr["BomId"] = BomId;
                ds.Tables["bom_bom_extradefine"].Rows.Add(dr);
            }

            {
                DataRow dr = ds.Tables["bom_bom"].NewRow();
                dr["BomId"] = BomId;
                dr["BomType"] = 1;
                dr["Version"] = bom.Version;
                dr["VersionDesc"] = bom.Version;
                dr["VersionEffDate"] =bom.VersionEffDate;
                dr["VersionEndDate"] = "2099-12-31";
                dr["CreateDate"] = DateTime.Now.ToString("yyyy-MM-dd");
                dr["CreateUser"] = bom.cmaker;
                dr["VTid"] = 30443;
                dr["Status"] = 3;
                dr["RelsDate"] = DateTime.Now.ToString("yyyy-MM-dd");
                dr["RelsUser"] = bom.cmaker;
                dr["RelsTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                dr["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                dr["AuditStatus"] = 1;
                dr["UpdCount"] = 0;
                dr["iPrintCount"] = 0;
                dr["IsWFControlled"] = 0;
                dr["iVerifyState"] = 0;
                dr["iReturnCount"] = 0;
                dr["bProcessProduct"] = 0;
                dr["bProcessMaterial"] = 0;

                ds.Tables["bom_bom"].Rows.Add(dr);
            }
            int iRow = 1;
            foreach (var row in bom.InventorySub)
            {
                long OpComponentId = 0L, OptionsId = 0L;
                GetVoucherID(DBName.Substring(7, 3), "bom_opcomponent", ref OpComponentId);
                GetVoucherID(DBName.Substring(7, 3), "bom_opcomponentopt", ref OptionsId);
                {
                    DataRow dr = ds.Tables["bom_opcomponent"].NewRow();
                    dr["OpComponentId"] = OpComponentId;
                    dr["BomId"] = BomId;
                    dr["SortSeq"] = iRow * 10;
                    dr["OpSeq"] = "0000";
                    dr["ComponentId"] = GetInvPartId(row.cInvCodeSub, DBName); //存货的partid
                    dr["EffBegDate"] = bom.VersionEffDate;
                    dr["EffEndDate"] = "2099-12-31";
                    dr["FVFlag"] = 1;
                    dr["BaseQtyN"] = row.BaseQtyN;
                    dr["BaseQtyD"] = 1;
                    dr["CompScrap"] = row.CompScrap;
                    dr["ByproductFlag"] = 0;
                    dr["OptionsId"] = OptionsId;
                    dr["ProductType"] = 1;
                    dr["bProcessProduct"] = 0;
                    dr["bProcessMaterial"] = 0;
                    dr["RecursiveFlag"] = 0;

                    for (int i = 1; i <= 10; i++)
                    {
                        dr["Free" + i] = "";
                    }
                    ds.Tables["bom_opcomponent"].Rows.Add(dr);
                    iRow++;
                }

                {
                    DataRow dr = ds.Tables["bom_opcomponentopt"].NewRow();
                    dr["OptionsId"] = OptionsId;
                    dr["Offset"] = 0;
                    dr["WIPType"] = 3;
                    dr["AccuCostFlag"] = 1;
                    dr["OptionalFlag"] = 0;
                    dr["MutexRule"] = 3;
                    dr["PlanFactor"] = 100;
                    dr["CostWIPRel"] = 0;
                    dr["FeatureRel"] = 0;

                    ds.Tables["bom_opcomponentopt"].Rows.Add(dr);
                }

                {
                    DataRow dr = ds.Tables["bom_opcomponent_extradefine"].NewRow();
                    dr["OpComponentId"] = OpComponentId;
                    dr["cBDefine100"] = row.cBDefine100;
                    ds.Tables["bom_opcomponent_extradefine"].Rows.Add(dr);
                }
            }

            //保存数据
            foreach (DataTable dt in ds.Tables)
            {
                SqlHelper.SaveTable(dt, Conn, Tran, null, DBName);
            }
        }



        public DataSet GetModel(string cInvCode)
        {
            DataSet ds = new DataSet();
            string[] tbls = "bas_part,inventory_sub,inventory_extradefine,inventory".Split(',');
            foreach (string tbl in tbls)
            {
                try
                {
                    string sql = string.Format("Select * from {0} With(nolock) Where {1} = '{2}'", tbl, GetInventoryTableKeyField(tbl), cInvCode);
                    DataTable dt = SqlHelper.Query(sql, strConn);
                    dt.TableName = tbl;
                    ds.Tables.Add(dt);
                }
                catch (Exception ex)
                {
                }
            }
            return ds;
        }

        public DataSet GetBOMModel()
        {
            DataSet ds = new DataSet();
            string[] tbls = "bom_bom,bom_parent,bom_opcomponent,bom_opcomponentopt,bom_bom_extradefine,bom_opcomponent_extradefine".Split(',');
            foreach (string tbl in tbls)
            {
                try
                {
                    string sql = string.Format("Select * from {0} With(nolock) Where 1=2 ", tbl);
                    DataTable dt = SqlHelper.Query(sql, strConn);
                    dt.TableName = tbl;
                    ds.Tables.Add(dt);
                }
                catch (Exception ex)
                {
                }
            }
            return ds;
        }

        private string GetInventoryTableKeyField(string TableName)
        {
            switch (TableName.ToLower())
            {
                case "bas_part":
                    return "invcode";
                case "inventory_sub":
                    return "cInvSubCode";
                case "inventory_extradefine":
                    return "cinvcode";
                case "inventory":
                    return "cinvcode";
                default:
                    return "id";
            }
        }


        private bool ExistsInv(string cInvCode, string DBName)
        {
            try
            {
                string sql = string.Format("Select 1 from {0}..Inventory with(nolock) Where cInvCode ='{1}' ", DBName, cInvCode);
                DataTable dt = SqlHelper.Query(sql, strConn);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        private bool Quary(string sql, SqlConnection Conn, SqlTransaction Tran)
        {
            try
            {
                int a = SqlHelper.Execute(sql, Conn, Tran);
                if (a > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private string GetInvPartId(string cInvCode, string DBName)
        {
            string sql = string.Format("Select PartId from {0}..bas_Part with(nolock) Where InvCode='{1}'", DBName, cInvCode);
            DataTable dt = SqlHelper.Query(sql, strConn);
            if (dt.Rows.Count == 0)
                throw new Exception("存货编码[" + cInvCode + "]不存在");
            return dt.Rows[0][0].ToString();
        }



        public bool GetVoucherID(string accId, string VouchType, ref long FatherID)
        {
            string strErr = "";
            long id = 0L;
            return GetVoucherID(strConn, accId, VouchType, 1, ref id, ref FatherID, ref strErr);
        }

        public bool GetVoucherID(string strConn, string accId, string VouchType, int Rows, ref long FatherID, ref long ChildID, ref string errMsg)
        {
            Boolean result = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand GetID = conn.CreateCommand();
                    GetID.CommandType = CommandType.StoredProcedure;
                    GetID.CommandText = "sp_GetID";
                    GetID.Parameters.Add("@RemoteId", SqlDbType.VarChar, 2, "00");
                    GetID.Parameters.Add("@cAcc_Id", SqlDbType.VarChar, 3).Value = accId;
                    GetID.Parameters.Add("@cVouchType", SqlDbType.VarChar, 50).Value = VouchType;
                    GetID.Parameters.Add("@iAmount", SqlDbType.Int, 1).Value = Rows.ToString();
                    GetID.Parameters.Add("@iFatherId", SqlDbType.Int);
                    GetID.Parameters.Add("@iChildId", SqlDbType.Int);
                    GetID.Parameters["@iFatherId"].Direction = ParameterDirection.Output;
                    GetID.Parameters["@iChildId"].Direction = ParameterDirection.Output;
                    GetID.ExecuteNonQuery();
                    FatherID = Int64.Parse(GetID.Parameters["@iFatherId"].Value.ToString());
                    ChildID = Int64.Parse(GetID.Parameters["@iChildId"].Value.ToString()) - Rows;
                    GetID.Dispose();
                    result = true;
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            return result;
        }
    }


    public class InventoryInfo
    {
        public string cInvCode { get; set; }

        public string BomID { get; set; }
        public decimal ParentScrap { get; set; }  //损耗
        public string Version { get; set; }  //版本号

        public string VersionEffDate { get; set; }  //版本号
        
        public List<InventorySub1> InventorySub { get; set; }

        public string cmaker { get; set; }
    }

    public class InventorySub1
    {
        public string cInvCodeSub { get; set; }
        public decimal BaseQtyN { get; set; }  //基本用量

        public string cBDefine100 { get; set; }

        public decimal CompScrap { get; set; }


    }

}
